﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using _4T.Data;
using CMS.Core.Libs;
using _4T.Data.IDataContext;

namespace _4T.DAL
{
    public class ApproveProfileDAL
    {

        public IEnumerable<CMSApproveProfile> GetListProfileApprove()
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "sp_GetAllProfileApprove";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            var reader = cmd.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader.DatareaderToList(n => new CMSApproveProfile
                {
                    ApproveProfileID = n["ApproveProfileID"] is DBNull ? Guid.Empty : Guid.Parse(n["ApproveProfileID"].ToString()),
                    ApproveName = n["ApproverName"] is DBNull ? string.Empty : n["ApproverName"].ToString(),
                    BenefitMoney = n["BenefitMoney"] is DBNull ? string.Empty : n["BenefitMoney"].ToString(),
                    Comment = n["Comment"] is DBNull ? string.Empty : n["Comment"].ToString(),
                    ConsiderAndDecideID = n["ConsiderAndDecideID"] is DBNull ? Guid.Empty : Guid.Parse(n["ConsiderAndDecideID"].ToString()),
                    ImageUrl = n["ImageUrl"] is DBNull ? string.Empty : n["ImageUrl"].ToString(),
                    VideoUrl = n["VideoUrl"] is DBNull ? string.Empty : n["VideoUrl"].ToString(),
                    Status = n["Status"] is DBNull ? (byte)0 : Convert.ToByte(n["Status"]),
                    ProfileID = n["ProfileID"] is DBNull ? string.Empty : n["ProfileID"].ToString(),
                    CreatedDate = n["CreatedDate"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(n["CreatedDate"].ToString())
                });
            }
            else
            {
                return new List<CMSApproveProfile>();
            }
        }

        public CMSApproveProfile GetProfileApproveByApproveProfileID(string ApproveProfileID)
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "sp_GetProfileApproveByProfileID";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@ApproveProfileID", SqlDbType.NVarChar)).Value = ApproveProfileID;
            cmd.Connection = conn;
            var reader = cmd.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader.DatareaderToList(n => new CMSApproveProfile
                {
                    ApproveProfileID = n["ApproveProfileID"] is DBNull ? Guid.Empty : Guid.Parse(n["ApproveProfileID"].ToString()),
                    ApproveName = n["ApproverName"] is DBNull ? string.Empty : n["ApproverName"].ToString(),
                    BenefitMoney = n["BenefitMoney"] is DBNull ? string.Empty : n["BenefitMoney"].ToString(),
                    Comment = n["Comment"] is DBNull ? string.Empty : n["Comment"].ToString(),
                    ConsiderAndDecideID = n["ConsiderAndDecideID"] is DBNull ? Guid.Empty : Guid.Parse(n["ConsiderAndDecideID"].ToString()),
                    ImageUrl = n["ImageUrl"] is DBNull ? string.Empty : n["ImageUrl"].ToString(),
                    VideoUrl = n["VideoUrl"] is DBNull ? string.Empty : n["VideoUrl"].ToString(),
                    Status = n["Status"] is DBNull ? (byte)0 : Convert.ToByte(n["Status"]),
                    ProfileID = n["ProfileID"] is DBNull ? string.Empty : n["ProfileID"].ToString(),
                    CreatedDate = n["CreatedDate"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(n["CreatedDate"].ToString())
                }).SingleOrDefault();
            }
            else
            {
                return new CMSApproveProfile();
            }
        }

        public CMSApproveProfile GetProfileApproveByApproveProfileName(string profileName)
        {
            var conn = new SqlConnection(Ultility.connectionString);
            conn.Open();
            var cmd = new SqlCommand();
            cmd.CommandText = "sp_GetProfileApproveByProfileName";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@ProfileID", SqlDbType.NVarChar)).Value = profileName;
            cmd.Connection = conn;
            var reader = cmd.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                return reader.DatareaderToList(n => new CMSApproveProfile
                {
                    ApproveProfileID = n["ApproveProfileID"] is DBNull ? Guid.Empty : Guid.Parse(n["ApproveProfileID"].ToString()),
                    ApproveName = n["ApproverName"] is DBNull ? string.Empty : n["ApproverName"].ToString(),
                    BenefitMoney = n["BenefitMoney"] is DBNull ? string.Empty : n["BenefitMoney"].ToString(),
                    Comment = n["Comment"] is DBNull ? string.Empty : n["Comment"].ToString(),
                    ConsiderAndDecideID = n["ConsiderAndDecideID"] is DBNull ? Guid.Empty : Guid.Parse(n["ConsiderAndDecideID"].ToString()),
                    ImageUrl = n["ImageUrl"] is DBNull ? string.Empty : n["ImageUrl"].ToString(),
                    VideoUrl = n["VideoUrl"] is DBNull ? string.Empty : n["VideoUrl"].ToString(),
                    Status = n["Status"] is DBNull ? (byte)0 : Convert.ToByte(n["Status"]),
                    ProfileID = n["ProfileID"] is DBNull ? string.Empty : n["ProfileID"].ToString(),
                    CreatedDate = n["CreatedDate"] is DBNull ? DateTime.MinValue : Convert.ToDateTime(n["CreatedDate"].ToString())
                }).SingleOrDefault();
            }
            else
            {
                return new CMSApproveProfile();
            }
        }

        public int AddApproveProfile(Guid approveProfileID, string approverName, byte status, string comment, string imageUrl, string videoUrl, DateTime editedTime,
            string profileID, Guid considerAndDecideID, string benefitMoney)
        {
            int result = 0;
            ConnectionStringSettings ConnectionStringSettings =
              ConfigurationManager.ConnectionStrings["cnnString4T"];
            if (ConnectionStringSettings != null && !string.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                string conString = ConnectionStringSettings.ConnectionString;

                SqlConnection conn = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand();
                try
                {
                    conn.Open();
                    cmd.CommandText = "sp_AddApproveProfile";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ApproveProfileID", SqlDbType.UniqueIdentifier).Value = approveProfileID;
                    cmd.Parameters.Add("@ApproverName", SqlDbType.NVarChar).Value = approverName;
                    cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = status;
                    cmd.Parameters.Add("@Comment", SqlDbType.NVarChar).Value = comment;
                    cmd.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = imageUrl;
                    cmd.Parameters.Add("@VideoUrl", SqlDbType.NVarChar).Value = videoUrl;
                    cmd.Parameters.Add("@ProfileID", SqlDbType.NVarChar).Value = profileID;
                    cmd.Parameters.Add("@ConsiderAndDecideID", SqlDbType.UniqueIdentifier).Value = considerAndDecideID;
                    cmd.Parameters.Add("@BenefitMoney", SqlDbType.NVarChar).Value = benefitMoney;
                    cmd.Parameters.Add("@EditTime", SqlDbType.DateTime).Value = editedTime;
                    cmd.Connection = conn;

                    result = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    cmd.Dispose();
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
            }
            return result;
        }
    }
}